# Chapter 8. DBI Shell and Database Proxying

#### Contents:

dbish -- The DBI Shell
Database Proxying

This chapter takes a look at two essential additions to the Perl DBI armory: a command-line shell for databases, and the proxying drivers that provide network access to remote database drivers.

## 8.1. dbish -- The DBI Shell

The DBI Shell, or `dbish` , is a command-line tool that allows you to run arbitrary SQL statements and diagnostics against databases without needing to write a complete Perl program.

For example, let's say we wanted to get a quick list of all the megaliths in Wiltshire. We could write a complete Perl program that connects to the database, prepares and executes the appropriate SQL statement, fetches the data back, formats it, and disconnects from the database.

With the DBI, this process is easy, but it's a bit tedious if you just want some quick information.

This is where the `dbish` comes into play. `dbish` allows you to connect to a data source and type an SQL statement straight into it. `dbish` handles all the underlying connecting, preparing, and executing, and also gives you the results right away.

### 8.1.1. Starting Up dbish

`dbish` is an executable program bundled with the DBI. You should be able to start it up by typing:

>     dbish

which will return a prompt in the following manner:

>     DBI::Shell 10.5 using DBI 1.14

>     WARNING: The DBI::Shell interface and functionality are
>     =======  very likely to change in subsequent versions!

>     Available DBI drivers:
>      1: dbi:ADO
>      2: dbi:ExampleP
>      3: dbi:Oracle
>      4: dbi:Proxy
>     Enter driver name or number, or full 'dbi:...:...' DSN:

Some drivers require real username and password authentication to connect to databases. To support this requirement, you can supply additional arguments to `dbish` in the form of:

>     dbish <data_source> [username] [password]

For example:

>     dbish '' stones stones

or:

>     dbish dbi: stones stones

In this case, we haven't specified a driver, and so we'll choose one interactively through the menus. We can also bypass the menus by putting in the data source name for the desired database:

>     dbish dbi:Oracle:archaeo stones stones

If you don't specify a driver on the command line, the displayed menus allow you to select a type of database by listing the various drivers available. For example, if an Oracle database contained the megalithic database, you would select the `dbi:Oracle` data source by typing `3`. This will result in that specific database driver being queried for available data sources. For example:

>     Enter data source to connect to:
>      1: dbi:Oracle:archaeo
>      2: dbi:Oracle:sales
>     Enter data source or number, or full 'dbi:...:...' DSN:

This example shows that the underlying Oracle database driver is aware of two locally configured Oracle databases. Our megalithic database is stored in the `archaeo` database, so type `1`.

At this stage, `dbish` will attempt to connect to the database. Once you have connected successfully to a data source, you will see a prompt such as:

>     stones@dbi:Oracle:archaeo>

telling you that you are connected to the data source `dbi:Oracle:archaeo` as the user `stones`, and that `dbish` is ready for you to issue commands to it.

You can make a connection to another database from within `dbish` by using the `/connect` command. For example:

>     stones@dbi:Oracle:archaeo> /connect dbi:Oracle:sales dbusername
>     Disconnecting from dbi:Oracle:archaeo.
>     Connecting to 'dbi:Oracle:sales' as 'dbusername'...
>     Password for 'dbusername' (not echoed to screen): ......
>     stones@dbi:Oracle:sales>

Unfortunately, connecting to multiple databases simultaneously is not yet supported by `dbish`.

### 8.1.2. Handling Statements

In general, the most common reason for using `dbish` is to issue ad-hoc SQL statements to a database, either to check that the statement works before including it in a Perl program, or just to get some quick answers. This task is exactly what `dbish` was designed for.

`dbish` commands are entered as a forward slash (`/`) followed by a command name and optionally some extra arguments. For example:

>     /help

Anything entered that doesn't start with a forward slash is considered to be part of an SQL statement and is appended to a ``statement buffer.'' Once the SQL statement is complete, you can execute it, and the results, if any, will be returned to your screen.

For example, to query the names of all sites in the megalithic database, type:

>     stones@dbi:Oracle:archaeo> SELECT name FROM megaliths
>     Current statement buffer (enter '/' to execute or '/help' for help):
>     SELECT name FROM megaliths

>     stones@dbi:Oracle:archaeo> /
>     'Avebury'
>     'Stonehenge'
>     'Lundin Links'
>     ...
>     [132 rows of 1 fields returned]
>     stones@dbi:Oracle:archaeo>

Note that a forward slash by itself can be used to execute statements. After executing a statement, the statement buffer is cleared. But suppose we start typing in a new query and then change our minds about what we want to return:

>     stones@dbi:Oracle:archaeo> SELECT name FROM megaliths
>     Current statement buffer (enter '/' to execute or '/help' for help):
>     SELECT name FROM megaliths
>     stones@dbi:Oracle:archaeo> SELECT name, mapref FROM megaliths
>     Current statement buffer (enter '/' to execute or '/help' for help):
>     SELECT name FROM megaliths
>     SELECT name, mapref FROM megaliths

>     stones@dbi:Oracle:archaeo>

This is totally wrong! Fortunately, you can clear the statement buffer of old statements and start new ones afresh with the `/clear` command. Statements that have been executed are automatically cleared from the statement buffer, but can be recalled with the `/history` command. You can even use the `/edit` command to start up an external editor for editing your SQL.

The way in which results of `SELECT` statements are displayed is also configurable using the `/format` command. The two options currently available are `/format` `neat` and `/format` `box`. The default option is `neat`, which uses the `DBI::neat_list()` function to format the data. For example, the statement:

>     stones@dbi:Oracle:archaeo> SELECT name, mapref FROM megaliths /

has the following output:

>     'Avebury', 'SU 102 699'
>     'Stonehenge' 'SU 123 422',
>     'Lundin Links', 'NO 404 027'
>     ...
>     [132 rows of 1 fields returned]

The `box` option is prettier:

>     +--------------+------------+
>     | name         | mapref     |
>     +--------------+------------+
>     | Avebury      | SU 102 699 |
>     +--------------+------------+
>     | Stonehenge   | SU 123 422 |
>     +--------------+------------+
>     | Lundin Links | NO 404 027 |
>     +--------------+------------+

It's also possible to issue non-`SELECT` statements from `dbish` with the `/` command. Want to delete all the rows from a table? Simply type:

>     stones@dbi:Oracle:archaeo> delete from megaliths /
>     [132 rows affected]
>     stones@dbi:Oracle:archaeo>

Quick, easy, and very deadly! Any non-`SELECT` statement can be issued in this way, including `CREATE TABLE` statements or even stored procedure calls, if your database supports them.[67]

> [67]There's a `/do` command that forces the `do()` method to be used instead of a `prepare()` followed by an `execute()`. In practice, it's rarely needed.

### 8.1.3. Some Miscellaneous dbish Commands

As `dbish` is a fairly fully featured command-line shell,[68] it has some convenient commands defined within it that allow you to commit and roll back database changes, recall statements and commands that you'd executed in the past, and even execute arbitrary Perl statements!

> [68]`dbish`'s powerful command-line editing functionality comes courtesy of the `Term::Readline` and `Term::Readline::Gnu` modules. You don't need to install them to use `dbish`, but it helps.

One of the most useful of the miscellaneous statements is `/table_info` , which lists the tables in the database that you are currently connected to. This statement is indispensable when you're trying to remember exactly what that pesky table name is!

A full list of these commands can be seen by typing the all-important `/help` command.

`dbish` is currently a handy tool for performing quick tasks on a database. It should continue to evolve over time into an indispensable part of the database administrator's and database developer's armory, much like proprietary tools such as Oracle's SQL*Plus utility.

Copyright © 2001 O'Reilly & Associates. All rights reserved.